<?php
namespace core;

use core\db\PDO;

/**
 * 数据库操作类
 * Class Db
 * @package core
 */
class Db
{
    //当前mysqli实力对象
    protected static $instance = null;

    //连接句柄
    protected $connect = null;

    //连接状态
    protected $connect_status = false;

    //数据库连接错误信息
    protected $connect_error = "";

    //sql错误信息
    protected $error = "";

    protected $table_name = "";

    protected $where = "";

    protected $fields = "*";

    protected $last_sql = "";

    protected $limit = "";

    protected $order = "";

    protected $having = "";

    protected $group = "";

    /**
     * 清除sql
     */
    function clearSql(){
        $this->table_name = "";
        $this->where = "";
        $this->last_sql = "";
        $this->limit = "";
        $this->order = "";
        $this->having = "";
        $this->group = "";
    }
    /**
     * Mysql constructor.
     * @param $host
     * @param $username
     * @param $passwd
     * @param $dbname
     * @param $port
     */
    private function __construct($host,$username,$passwd,$dbname,$port){
        // 创建连接
        $this->connect = new \mysqli($host, $username, $passwd,$dbname,$port);

        // 检测连接
        if ($this->connect->connect_error) {
            $this->connect_error = $this->connect->connect_error;
        }else{
            $this->connect_status = true;
        }
    }

    /**
     * 关闭外部克隆，实现单列模式
     */
    private function __clone(){}

    /**
     * 连接数据库
     * @param $host
     * @param $username
     * @param $passwd
     * @param $dbname
     * @param string $port
     * @return Mysql|null
     */
    static function connect(){
        if(self::$instance===null){
            $database = Config::get('database');
            self::$instance = new DB($database['hostname'],$database['username'],$database['password'],$database['database'],$database['port']);
            if(self::$instance->connect_status){
                self::$instance->execute('SET NAMES ' . $database['charset']);//设置字符集
            }
        }

        return self::$instance;
    }

    /**
     * 关闭数数据库
     */
    static function close(){
        if(self::$instance!==null){
            if(self::$instance->connect_status){
                self::$instance->connect->close();
            }
        }
    }

    /**
     * 返回最后的错误信息
     * @return string
     */
    function getLastError(){
        return $this->connect_error.$this->error;
    }

    function getLastSql(){
        return $this->last_sql;
    }

    /**
     * 执行sql、用于除了select语言的sql
     * @param $sql
     * @return bool
     */
    function execute($sql){
        $this->clearSql();
        if ($this->connect->query($sql) === TRUE) {
            return true;
        } else {
            $this->error = $this->connect->error;
            Log::write($this->error);
            return false;
        }
    }

    /**
     * 查询数据，用于select语句
     * @param $sql
     * @return array
     */
    function query($sql){
        $this->clearSql();
        $data = array();
        if($this->connect->connect_errno){
            Log::write("数据库连接失败");
            return $data;
        }
        $result = $this->connect->query($sql);
        if ($result->num_rows > 0) {
            // 输出数据
            while($row = $result->fetch_assoc()) {
                $data[] = $row;
            }
        }

        return $data;
    }

    /**
     * 新增数据
     * @param $dataArr 新增数据数组
     * @return false|int 返回受影响的行数
     */
    function insert($dataArr){
        $fieldStr = '';
        $valuesStr = '';
        $sql = '';

        foreach ($dataArr as $key=>$val){
            $fieldStr .= ',`'.$key.'`';
            $valuesStr .= ',"'.$val.'"';
        }

        $fieldStr = substr($fieldStr,1);
        $valuesStr = substr($valuesStr,1);

        if($fieldStr&&$valuesStr){
            $sql = 'INSERT INTO '.$this->table_name.' ('.$fieldStr.') VALUES ('.$valuesStr.')';
        }

        if($sql){
            $this->last_sql = $sql;
            if ($this->connect->query($sql) === true) {
                return true;
            } else {
                $this->error = $this->connect->error;
                Log::write($this->error);
                return false;
            }
        }

        return false;
    }

    /**
     * 更新数据
     * @param $update_data
     * @param $where
     * @return false|int 返回更新的记录数
     */
    function update($update_data,$where=null){
        $setStr = '';
        $sql = '';

        foreach ($update_data as $key=>$val){
            $value = (!is_string($val))?$val:"'".$val."'";//如果不是数值，加引号
            $setStr .= ",`".$key."`=".$value;
        }
        $setStr = substr($setStr,1);

        if($setStr){
            $sql = 'UPDATE '.$this->table_name.' SET '.$setStr;
        }

        if($sql && $where!==true){
            $whereStr = $this->orgWhereSql($where);
            if(!$whereStr){
                $whereStr = "1=2";
            }
            $sql .= ' WHERE '.$whereStr;
        }

        $this->last_sql = $sql;

        return $this->execute($sql);
    }

    /**
     * delete 删除数据
     * @return false|int
     */
    public function delete($where=null){

        $sql = 'DELETE FROM `' . $this->table_name . '`';

        if($where!==true){
            $whereStr = $this->orgWhereSql($where);
            if($whereStr){
                $sql .= ' WHERE '.$whereStr;
            }else{
                $sql .= ' WHERE 1=2';
            }
        }

        $this->last_sql = $sql;
        return $this->execute($sql);
    }

    /**
     * 查询数据集
     * @return array
     */
    function select(){
        //select ... from... where.... group by... having... order by.. limit [offset,]
        $sql = "SELECT ".$this->fields." FROM ".$this->table_name;

        if($this->where){
            $sql .= " WHERE ".$this->where;
        }

        if($this->group){
            $sql .= " GROUP BY ".$this->group;
        }

        if($this->having){
            $sql .= " HAVING".$this->having;
        }

        if($this->order){
            $sql .= " ORDER BY ".$this->order;
        }

        if($this->limit){
            $sql .= " LIMIT ".$this->limit;
        }
        $this->where = "";
        return $this->query($sql);
    }

    /**
     * 查询
     * @return array
     */
    function count(){
        $sql = "SELECT COUNT(1) AS count FROM ".$this->table_name;

        if($this->where){
            $sql .= " WHERE ".$this->where;
        }

        if($this->group){
            $sql .= " GROUP BY ".$this->group;
        }

        if($this->having){
            $sql .= " HAVING".$this->having;
        }

        $data = $this->query($sql);
        $this->where = "";
        return $data[0]['count'];
    }

    /**
     * 查询一条数据
     * @return array
     */
    function find(){
        $this->limit = 1;
        $rows = $this->select();
        if($rows){
            return $rows[0];
        }else{
            return null;
        }
    }


    /**
     * 设置表名
     * @param $table_name
     * @return $this
     */
    function table($table_name){
        $this->table_name = $table_name;
        return $this;
    }

    /**
     * 设置查询字段
     * @param $fields
     * @return $this
     */
    function setFields($fields){
        if($fields){
            $fields_str = '';
            if(is_array($fields)){
                $fields_str = implode(',',$fields_str);//把数组以,分割为字符串
            }

            $fields_str = str_replace('`','',$fields_str);
            $fields_str = str_replace(',','`,`',$fields_str);
            $fields_str = '`'.$fields_str.'`';

            if($fields_str){
                $this->fields = $fields_str;
            }
        }
        return $this;
    }

    /**
     * 查询条件
     * @param $where
     * @return $this
     */
    function where($where=null){
        $where_str = $this->orgWhereSql($where);
        if($where_str){
            if($this->where){
                $this->where .= " AND (" . $where_str.")";
            }else{
                $this->where = $where_str;
            }
        }

        return $this;
    }

    /**
     * 排序
     * @param $order
     * @return $this
     */
    function order($order=null){
        if(!empty($order)){
            $order_str = '';
            if(is_array($order)){
                $i = 0;
                foreach ($order as $key=>$val){
                    if($i){
                        $order_str .= ',`' . $key . '` ' . $val;
                    }else{
                        $order_str .= '`' . $key . '` ' . $val;
                    }
                    $i++;
                }
            }elseif(is_string($order)){
                $order_str = $order;
            }

            if($order_str){
                $this->order = $order_str;
            }
        }
        return $this;
    }

    /**
     * 分组
     * @param $group
     * @return $this
     */
    function group($group){
        $this->group = $group;
        return $this;
    }

    /**
     * having
     * @param $having
     * @return $this
     */
    function having($having){
        $this->having = $having;
        return $this;
    }

    /**
     * 查询条数
     * @param $limit
     * @return $this
     */
    function limit($limit){
        $this->limit = $limit;
        return $this;
    }

    /**
     * 分页
     * @param int $page 页面
     * @param int $count 每页条数
     * @return $this
     */
    function page($page=1,$count=15){
        $this->limit = ($page-1)*$count . "," . $count;
        return $this;
    }

    /**
     * 拼装条件sql
     * @param $where
     * @return string
     */
    private function orgWhereSql($where){
        $where_str = "";
        if(!empty($where)){
            if(is_array($where)){
                foreach ($where as $key=>$val){
                    $value = is_numeric($val)?$val:"'".$val."'";//如果不是数值，加引号
                    $str = is_numeric($key)?$val:'`'.$key.'`='.$value;//如果是键值对
                    $where_str .= $where_str?" AND ".$str:$str;
                }
            }elseif(is_string($where)){
                $where_str = $where;
            }
        }

        return $where_str;
    }
}
